﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffFromTeam]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffFromTeam];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffFromTeam] 
    @StaffName nvarchar(300),
    @Inherit int =0,
    @Upsides int =0
/*

============================================================
功能:    得到@UserName所在组的成员或下级成员
参数:
    @UserName nvarchar(300)    :    用户名
    @Inherit int =0        :    继承关系
    @Upsides int =0        :    是否包含同级成员


============================================================

*/
AS
SET NOCOUNT ON
DECLARE @Staff_ID int
--得到用户ID
SET @Staff_ID =0
SELECT @Staff_ID = Staff_ID
    FROM uds_staff
    WHERE staff_name = @staffname

CREATE TABLE #TabStaff (staff_id int)
--把同级人员加入到列表
IF @Upsides & 1 = 1
    INSERT INTO #TabStaff (staff_id)
        SELECT staff_id FROM uds_staff_in_team  
        WHERE team_id in (select team_id FROM uds_staff_in_team WHERE staff_id =@Staff_ID)
--非同级列表
IF @Upsides & 2 = 2
BEGIN
    --只选定直属下级成员
    IF @Inherit & 1 =1 
        INSERT INTO #TabStaff (staff_id)
        SELECT staff_id FROM uds_staff_in_team  
        WHERE team_id in (SELECT CLASSID FROM uds_class WHERE classparentid in (select team_id FROM uds_staff_in_team WHERE staff_id =@Staff_ID))
    --递归定多层下级成员
    IF @Inherit & 2 =2
    BEGIN
        DECLARE @ids nvarchar(2000)
        DECLARE @tmpids nvarchar(2000)
        DECLARE @oldids nvarchar(2000)
        
        SET @ids =''
        SET @tmpids=''
        SET @oldids =''
        
        SELECT @ids = @ids + convert(nvarchar,classid) + ',' 
            FROM uds_class WHERE classparentid in (select team_id FROM uds_staff_in_team WHERE staff_id =@Staff_ID)

        IF len(@ids)>0
            SET @ids = left(@ids,len(@ids)-1)
        
        CREATE TABLE #team(id int)
        SET @oldids = @ids
        
        WHILE len(@oldids)>0
        BEGIN
            
            exec ('insert into #team select classid from uds_class where classparentid in (' + @oldids +')')
            SET @tmpids=''
            SELECT @tmpids = @tmpids + convert(nvarchar,id) + ',' from #team
            IF len(@tmpids)>0
                SET @tmpids = left(@tmpids,len(@tmpids)-1)
        
            SET @oldids = @tmpids
            delete from #team
            SET @ids = @ids + ',' + @tmpids
        END

        IF RIGHT(@ids,1)=','
            SET @ids = LEFT(@ids,LEN(@ids)-1)
        PRINT 'INSERT INTO #TabStaff (staff_id)
            SELECT staff_id FROM uds_staff_in_team 
            WHERE team_id IN (' + @ids + ')'
        
        EXEC('INSERT INTO #TabStaff (staff_id)
            SELECT staff_id FROM uds_staff_in_team 
            WHERE team_id IN (' + @ids + ')')
        
    END

END
--得到结果
SELECT * 
    FROM uds_staff 
    WHERE staff_id IN (
                SELECT staff_id 
                    FROM #tabStaff
               )

SET NOCOUNT OFF